import json

import allure, pymysql

dbinfo = {
    "host": "dohko.mysql.001.master.hualala.com",
    "user": "root",
    "password": "hualaladev",
    "port": 3306
}

# test_dbinfo = {
#     "host": "dohko.mysql.001.master.hualala.com",
#     "user": "root",
#     "password": "hualaladev",
#     "port": 3306
# }

class DbConnect():
    @allure.step("建立数据库连接")
    def __init__(self, db_cof, database="db_mis"):
        """打开数据库连接"""
        self.db_cof = db_cof
        self.db = pymysql.connect(database=database, cursorclass=pymysql.cursors.DictCursor, **db_cof)
        self.cursor = self.db.cursor()

    @allure.step("查询sql语句，所有数据")
    def select_all(self, sql):
        """sql查询s所有数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchall()    #查询所有的数据
        return results

    @allure.step("查询sql语句，最后一条数据")
    def select_lastOne(self, sql):
        """sql查询最后一条数据"""
        self.cursor.execute(sql)
        results = self.cursor.fetchone()    #查询最后一条数据
        return results

    @allure.step("执行sql语句")
    def execut(self, sql):
        """执行sql语句（增，删，改）"""
        try:
            """尝试执行"""
            self.cursor.execute(sql)
            self.db.commit()    #提交动作
        except:
            """发生错误时回滚"""
            self.db.rollback()

    @allure.step("关闭数据库连接")
    def close(self):
        self.db.close()


def select_sql_all(sql, db_cof=dbinfo, dbname="db_mis"):# dbname 是数据库名称
    """sql查询_所有数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_all(sql)
    db.close()
    return res

def select_sql_lastOne(sql, db_cof=dbinfo, dbname="dbinfo"):
    """sql查询_最后一条数据"""
    db = DbConnect(db_cof, dbname)
    res = db.select_lastOne(sql)
    db.close
    return res

def exectue_sql(sql, db_cof=dbinfo, dbname="dbinfo"):
    """执行sql语句"""
    db = DbConnect(db_cof, dbname)
    db.execut(sql)
    db.close



if __name__ == '__main__':
    sql = "select * from db_mis.tbl_mis_apply_process_urge where costOrderID = 12786"
    # sql1 = "DELETE from anti_addiction_template where name='中国网游防沉迷自动化模板名称1595494061'"
    # result = select_sql_all(sql, dbname="db_mis")
    # result1 = (result)
    # print(result1)
    # for k in result1:
    #     j = k
    #     print(k, result1[j])
    sql1 = "update tbl_mis_apply_process_urge set requestID = '1988' where requestID = '419976'"
    result = exectue_sql(sql1, dbname="db_mis")
    result1 = (result)
    print(json.dumps(result1, indent=1))

